#include "db.h"
#include <QtSql/QSqlDatabase>
#include <QDebug>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>
#include <QtSql/QSqlQuery>
#include <QVariant>

#define DEFAULT_CONNECTION  QString("DEFAULT_CONNECTION")

DB::DB(QObject *parent)
{
    database=QSqlDatabase::database(DEFAULT_CONNECTION);
    qDebug()<<"1: Open database with connection: "<<database.connectionName()<<" : "<<database.open();
    error(database,"DB::DB()");

}

DB::DB(QSqlDatabase &other, QObject *parent)
{
    qDebug()<<other.open();
    database=QSqlDatabase::database(DEFAULT_CONNECTION);
    qDebug()<<database.open();
    qDebug()<<database;
    qDebug()<<database.connectionName();

}

/*connect to db*/
DB::DB(const QString &name, Config *cfg, QObject *parent)
{
#ifdef  USE_MYSQL
    database=QSqlDatabase::addDatabase("QMYSQL",DEFAULT_CONNECTION);
    if(cfg)
    {
        QString userName=cfg->getVar(DB_MYSQL_USERNAME_CFG_VAR).first();
        QString password=cfg->getVar(DB_MYSQL_PASS_CFG_VAR).first();
        if(userName.isEmpty() || password.isEmpty())
        {
            qFatal("Verbindungsdaten für MySQL-Datenbank nicht vorhanden. Bitte Konfigurationsdatei überprüfen.");
            return;
        }
        database.setUserName(userName);
        database.setPassword(password);
    }
    else
    {
        database.setUserName("capcalc_client");
        database.setPassword("123456789");
    }
#endif

#ifdef USE_SQLITE
    database=QSqlDatabase::addDatabase("QSQLITE",DEFAULT_CONNECTION);
#endif

    database.setHostName("localhost");
    database.setDatabaseName(name);

    qDebug()<<"Open database with connection: "<<database.connectionName()<<" : "<<database.open();
    error(database,"DB::DB()");
}

DB::~DB()
{
    QSqlDatabase::removeDatabase(DEFAULT_CONNECTION);
    database.removeDatabase(DEFAULT_CONNECTION);
    database.close();
}

QSqlDatabase DB::getDatabase()
{
    return database;
}

void DB::setDatabase(const QSqlDatabase &other)
{
    database=other;
}

/*checks, if the given value is in the given field of the given table*/
bool DB::containsValue(const QString &table, const QString &field, const QString &val)
{
    QSqlQuery q(database);

    if(q.prepare("SELECT "+field+" FROM "+table+" WHERE "+field+"='"+val+"';"))
        return (q.exec()&q.first()&!q.value(0).isNull());

    error(q,"DB::containsValue()");
    return false;
}

/*checks, if one element of vals is in the field of table*/
bool DB::containsValue(const QString &table, const QString &field, const QStringList &vals)
{
    QSqlQuery q(database);

    if(q.prepare("SELECT "+field+" FROM "+table+" WHERE "+field+"='"+vals.join("' OR "+field+"='")+"';"))
        return (q.exec()&q.first()&!q.value(0).isNull());

    error(q,"DB::containsValue()");
    return false;
}

/*checks, if the given field is in the given table*/
bool DB::containsField(const QString &table, const QString &field)
{
    QSqlQuery q(database);

    if(q.prepare("SELECT "+field+" FROM "+table+";"))   //prepare a test-query
        return q.exec();                                //exex the query, success->true

    error(q,"DB::containsValue()");
    return false;
}

/*adds a table, if it doesn't exist*/
bool DB::addTable(const QString &name,const QStringList& fields)
{
    //qDebug()<<"Adde table";
    //qDebug()<<"Connection: "<<database;
    qDebug()<<"Table "<<name<<" with fields "<<fields.join(",")<<" creating";

    if(!database.tables().contains(QString(name.unicode())))         //no table in db
    {
        qDebug()<<"Creating table "<<name;
        QSqlQuery q(database);
        q.exec("CREATE TABLE "+QString(name.unicode())+" ("+PRIM_KEY+","+fields.join(" TEXT,")+" TEXT);");   //create table with given fields
        qDebug()<<"DB::addTable(): "<<"Query: "<<q.lastQuery();

        error(q,"DB::addTable");

        return (!(q.lastError().type()!=0));  //no error occured
    }

    qDebug()<<"DB::addTable(): "<<"Table "<<name<<" already exists";
    return false;
}

/*creates table if not exist and fills it with vals*/
bool DB::addValues(const QString &tablename, const QStringList &fields, const QStringList &vals,int flag)
{
    QSqlQuery q(database);

    qDebug()<<"Setup table";

    q.exec("INSERT INTO "+QString(tablename.unicode())+" ("+fields.join(",")+") VALUES ('"+vals.join("','")+"');");
    error(q,"DB::addValues()");
    /*for(int i=0;i<vals.length();i++)
    {
        qDebug()<<fields.at(i)<<" : "<<vals.at(i);
        rec.setValue(fields.at(i),QVariant::fromValue(vals.at(i)));  //add values
    }*/
    return (!database.lastError().type());
}

/*adds multiple values to multiple fields*/
bool DB::addValues(const QString &tablename, const QStringList &fields, const QList<QStringList> &vals)
{
    QSqlQuery q(database);
    QStringList queryStr;

    if(fields.count()==vals.count())        //bijective relation
    {
        for(int i=0;i<vals.at(0).size();i++)
        {
            queryStr.clear();

            for(int z=0;z<vals.size();z++)
            {

                queryStr<<vals.at(z).at(i);
            }

            q.exec("INSERT INTO "+QString(tablename.unicode())+" ("+fields.join(",")+") VALUES ('"+queryStr.join("','")+"');");
        }

    }
    error(q,"DB::addValues()");
    return (!database.lastError().type());
}

/*adds all values in vals into the given field of the given tablename*/
bool DB::addValues(const QString &tablename, const QString &field, const QStringList &vals)
{
    QSqlQuery q(database);

    for(int i=0;i<vals.size();i++)
        q.exec("INSERT INTO "+QString(tablename.unicode())+" ("+field+") VALUES ('"+vals.at(i)+"');");

    error(q,"DB::addValues()");
    return (!database.lastError().type());
}

/*an overloaded function*/
bool DB::addValues(const QString &tablename, const QStringList &fields, const QList<qreal> &vals)
{
    QSqlQuery q(database);
    QStringList valStr;

    for(int i=0;i<vals.size();i++)
    {
        valStr<<QString("%1").arg(vals.at(i));
    }

    q.exec("INSERT INTO "+QString(tablename.unicode())+" ("+fields.join(",")+") VALUES ('"+valStr.join("','")+"');");

    error(q,"DB::addValues()");
    return (!database.lastError().type());
}

bool DB::addValues(const QString &tablename, const QMap<QString, QVariant> &fieldVals)
{
    QSqlQuery q(database);

    QStringList fields=fieldVals.keys();
    QStringList valList;
    QList<QVariant> vals=fieldVals.values();

    for(int i=0;i<vals.count();i++)
        valList<<vals.at(i).toString();

    q.exec("INSERT INTO "+QString(tablename.unicode())+" ("+fields.join(",")+") VALUES ('"+valList.join("','")+"');");

    error(q,"DB::addValues()");
    return (!database.lastError().type());
}

//TODO!
bool DB::updateValues(const QString &tablename, const QString &field, const QStringList &vals,int flag)
{
    QSqlQuery q(database);

    for(int i=0;i<vals.size();i++)
    {
        if(flag==UPDATE_BY_LIST_INDEX)
            q.exec("UPDATE "+QString(tablename.unicode())+" SET "+field+"='"+vals.at(i)+"' WHERE ;");
    }

    error(q,"DB::updateValues()");
    return (!database.lastError().type());
}

bool DB::updateValue(const QString &tableName, const QString &field, const QString &value, const QString &whereClause)
{
    QSqlQuery query(database);

    query.exec("UPDATE "+tableName+" SET "+field+"='"+value+"' WHERE "+whereClause+";");

    error(query,"DB::updateValue()");
    return (!database.lastError().type());
}

/*saves all values of the column col from table into list*/
void DB::getColumn(QList<qreal> *list, const QString &col,const QString& table,bool checkNull)
{
    QSqlQuery query(database);

    if(checkNull)
        query.exec("SELECT "+col+" FROM "+table+" WHERE "+col+" IS NOT NULL;");   //get the column if it's not null
    else
        query.exec("SELECT "+col+" FROM "+table+";");   //get the column

    error(query,"DB::getColumn()");

    while(query.next())                             //go through the record
        list->append(query.value(0).toReal());      //add all values to the list
}

/*get all pair values out of two columns and save it into pointList(col1.val,col2.val)*/
void DB::getColumnPairs(QList<QPointF> * pointList, const QString &col1, const QString &col2,const QString& table, bool checkNull)
{
    QSqlQuery query(database);
    QString q;

    if(checkNull)
        q="SELECT "+col1+","+col2+" FROM "+table+" WHERE ("+col1+" IS NOT NULL) AND ("+col2+" IS NOT NULL);"; //get two columns which are not null
    else
        q="SELECT "+col1+","+col2+" FROM "+table+";"; //get two columns

    query.exec(q);  //execute command
    error(query,"DB::getColumnPairs()");

    while(query.next())                                                 //walk through the record
    {
        QPointF point(query.value(0).toReal(),query.value(1).toReal()); //generate point
        pointList->append(point);                                       //add point to list
    }

}

/*read column col from table and save it into list*/
void DB::getColumn(QStringList* list, const QString & col, const QString &table, bool checkNull)
{
    QSqlQuery query(database);

    if(checkNull)
        query.exec("SELECT "+col+" FROM "+table+" WHERE "+col+" IS NOT NULL;");   //get the column if it's not null
    else
        query.exec("SELECT "+col+" FROM "+table+";");   //get the column

    error(query,"DB::getColumn()");

    while(query.next())                             //go through the record
        list->append(query.value(0).toString());      //add all values to the list
}

/*read fields of a row from table with the given clause*/
QMap<QString,QVariant> DB::getRow(const QString &table, const QStringList &valFields, const QString &whereClause)
{
    QSqlQuery query(database);
    QMap<QString,QVariant> resMap;

    if(whereClause.isEmpty())
    {
        query.exec("SELECT "+valFields.join(",")+" FROM "+table+";");
    }
    else
    {
        query.exec("SELECT "+valFields.join(",")+" FROM "+table+" WHERE "+whereClause+";");
        error(query,"DB::getRow()");

    }

    resMap=getFieldMap(valFields,query).at(0);

    error(query,"DB::getRow()");

    return resMap;
}

QList<QMap<QString, QVariant> > DB::getAllRows(const QString &table, const QStringList &valFields)
{
    QSqlQuery query(database);

    query.exec("SELECT "+valFields.join(",")+" FROM "+table+";");
    error(query,"DB::getAllRows()");

    return getFieldMap(valFields,query);
}

QVariant DB::getValue(const QString &table, const QString &valField, const QString &whereClause)
{
    QSqlQuery query(database);

    query.exec("SELECT "+valField+" FROM "+table+" WHERE "+whereClause+";");
    error(query,"DB::getValue()");

    while(query.next())
    {
        if(query.value(0).isValid())
            return query.value(0);
    }
    return QVariant();
}

int DB::getPrimaryKey(const QString &table, const QString &lookUpField, const QString &lookUpValue)
{
    QStringList valFields;
    valFields<<"ID";
    QMap<QString,QVariant> tmp=getRow(table,valFields,lookUpField+"='"+lookUpValue+"'");
    qDebug()<<"GET_PRIMARY_KEY: "<<tmp;
    return tmp.value("ID").toInt();
}

QList<QMap<QString,QVariant> > DB::getInnerJoinValues(const QString &table1, const QString &table2, const QString &table1Key, const QString &table2Key, const QStringList &fields)
{
    QSqlQuery query(database);
    QString q;
    error(query,"DB::getInnerJoinValues()");

    q="SELECT "+fields.join(",")+" FROM "+table1+" INNER JOIN "+table2+" ON "+table1+"."+table1Key+"="+table2+"."+table2Key+";";

    query.exec(q);
    error(query,"DB::getInnerJoinValues()");

    return getFieldMap(fields,query);;
}

/*count of list is equal to count of rows*/
QList<QMap<QString,QVariant> > DB::getFieldMap(const QStringList& fields,QSqlQuery query)
{
    qDebug()<<"DB::getFieldMap: "<<"Fields: "<<fields.join(" | ")<<" Query: "<<query.lastQuery();
    QList<QMap<QString,QVariant> > mapList;
    QSqlRecord rec;
    rec=query.record();
    //look for a better way!
    while(query.next())
    {
        QMap<QString,QVariant> resMap;
        for(int i=0;i<fields.count();i++)
        {
            qDebug()<<"Looking for "<<fields.at(i);
            int tmp=rec.indexOf(fields.at(i));          //get index of field
            qDebug()<<"GET_FIELD_MAP: "<<fields.at(i)<<" : "<<query.value(tmp);
            resMap.insert(fields.at(i),query.value(tmp));   //insert value into the field map
        }
        mapList.append(resMap);
    }
    return mapList;
}

void DB::error(const QSqlQuery &q, const QString &funcName)
{
    if(q.lastError().isValid())
        if(!funcName.isEmpty())
            qCritical()<<funcName<<": "<<q.lastQuery()<<" : "<<q.lastError();
        else
            qCritical()<<q.lastQuery()<<" : "<<q.lastError();
}

void DB::error(const QSqlDatabase &db,const QString& funcName)
{
    if(db.lastError().isValid())
        if(!funcName.isEmpty())
            qCritical()<<funcName<<": "<<db.lastError();
        else
            qCritical()<<db.lastError();
}
